from datetime import datetime, timedelta
import pandas as pd
import match  # 假设这是一个自定义模块
import os
import logging

from utils.verifyParameters import verify

# 配置日志
logging.basicConfig(level=logging.INFO)


def process_xianyanbei_reconciliation(recon_start_time, recon_end_time, file_path, fault_tolerant, station_id, ignore_time):
    # region 配置
    station_config = {
        "name": "咸阳北",
        "ids": [5],
        "main_body_gas_station": 19,
        "file_keyword": "(.*)咸阳北(.*)",
        "columns": {
            "create_time": "日期",
            "create_time2": "加气时间",
            "car_number": "车牌号",
            "gas_num": "数量/kg",
            "gas_price": "优惠后价格/元",
        },
        "is_time_shuffled": True,  # 时间匹配减弱
    }

    verify(fault_tolerant, station_id, ignore_time, station_config)

    # 验证文件路径
    if not os.path.exists(file_path):
        logging.error(f"文件路径不存在: {file_path}")
        raise FileNotFoundError(f"文件路径不存在: {file_path}")

    # 处理时间范围
    try:
        start_time = datetime.strptime(recon_start_time, "%Y-%m-%d %H:%M:%S")
        start_time = (start_time - timedelta(hours=1)).strftime("%Y-%m-%d %H:%M:%S")
        end_time = datetime.strptime(recon_end_time, "%Y-%m-%d %H:%M:%S")
        end_time = (end_time + timedelta(hours=1)).strftime("%Y-%m-%d %H:%M:%S")
    except ValueError as e:
        logging.error(f"时间格式错误: {e}")
        raise

    # step 1 获取Excel中的可用sheet
    closest_sheet = None
    min_diff = None
    closest_sheet_name = None

    try:
        excel_file = pd.ExcelFile(file_path)
        sheet_names = excel_file.sheet_names
    except Exception as e:
        logging.error(f"读取Excel文件失败: {e}")
        raise

    for sheet in sheet_names:
        try:
            df_tmp = pd.read_excel(file_path, sheet_name=sheet)
        except Exception as e:
            logging.warning(f"读取sheet {sheet} 失败: {e}")
            continue

        header_row = None
        if all(item.strip() in df_tmp.columns.tolist() for item in list(station_config["columns"].values())):
            header_row = -1
        else:
            for row in df_tmp.head(13).itertuples():
                if all(item.strip() in [str(cell).strip() for cell in row[1:]] for item in
                       list(station_config["columns"].values())):
                    header_row = row.Index
                    break

        if header_row is not None:
            try:
                df = pd.read_excel(file_path, sheet_name=sheet, skiprows=header_row + 1)
                df.columns = [col.strip() for col in df.columns]
                df[station_config["columns"]["create_time"]] = pd.to_datetime(
                    df[station_config["columns"]["create_time"]], errors="coerce"
                )
                df = df.dropna(subset=[station_config["columns"]["create_time"]])
                df = df.dropna(subset=[station_config["columns"]["gas_num"]])
                latest_time = df[station_config["columns"]["create_time"]].max()
                if pd.notnull(latest_time):
                    time_diff = abs(
                        (latest_time.date() - datetime.strptime(recon_end_time, "%Y-%m-%d %H:%M:%S").date()).days
                    )
                    if min_diff is None or time_diff < min_diff:
                        min_diff = time_diff
                        closest_sheet = df
                        closest_sheet_name = sheet
            except Exception as e:
                logging.warning(f"处理sheet {sheet} 时出错: {e}")

    if closest_sheet is None:
        logging.error("未找到有效的sheet")
        raise ValueError("未找到有效的sheet")

    try:
        station_data = closest_sheet[list(station_config["columns"].values())]
        station_data = station_data[station_data[station_config["columns"]["gas_num"]].notnull() & station_data[
            station_config["columns"]["create_time"]].notnull()]
    except Exception as e:
        logging.error('配置文件与excel不一致')
        raise

    # 本站特殊处理
    def normalize_datetime(dt_str):
        try:
            date, time = dt_str.split()
            time_parts = time.split('.')
            time_parts = [part.zfill(2) for part in time_parts]  # 补齐两位
            return pd.to_datetime(f"{date} {'.'.join(time_parts)}", format='%Y-%m-%d %H.%M.%S').strftime(
                '%Y-%m-%d %H:%M:%S')
        except Exception as e:
            logging.warning(f"时间格式化失败: {dt_str}, 错误: {e}")
            return None

    station_data = station_data[
        station_data[station_config["columns"]["create_time2"]].str.match(r'^\d{1,2}\.\d{1,2}\.\d{1,2}$', na=False)]
    station_data['日期时间'] = (station_data[station_config["columns"]["create_time"]].astype(str) + ' ' + station_data[
        station_config["columns"]["create_time2"]].astype(str)).apply(normalize_datetime)
    station_data = station_data.dropna(subset=['日期时间'])
    station_config["columns"]["create_time"] = "日期时间"
    logging.info(f"数据量: {len(station_data)}, sheet名: {closest_sheet_name}")
    station_data = match.set_station_id_column(station_data, station_config)
    station_dfs_dict = {}

    # step 2 处理Excel 如果是多个气站并且Excel中的字段配置包含station_name，就需要分多个气站进行对比。
    if "station_name" in station_config["columns"] and len(station_config["ids"]) > 1 and "nms" in station_config:
        station_names = station_config["nms"]
        station_name_col = station_config["columns"]["station_name"]
        for idx, pattern in enumerate(station_names):
            matched_df = station_data[station_data[station_name_col].astype(str).str.match(pattern, na=False)]
            non_midnight_count = (
                    matched_df[station_config["columns"]["create_time"]].dt.strftime("%H:%M:%S") != "00:00:00"
            ).sum()
            if len(matched_df) > 0 and (non_midnight_count / len(matched_df)) > 0.5:
                matched_df = matched_df.sort_values(by=station_config["columns"]["create_time"])
            station_dfs_dict[station_config["ids"][idx]] = matched_df

    match_result = []

    # step 3 如果有station_dfs_dict就循环这个，然后每个站点单独获取数据并对比，否则就获取配置中的所有站点数据并进行对比
    if len(station_dfs_dict) > 0:
        for i in station_dfs_dict:
            online_data = match.get_online_data([i], start_time, end_time)
            match_result.append(match.match_data_v1(station_dfs_dict[i], online_data, station_config))
    else:
        if station_config.get("excel_get_time_range", False):
            station_data = station_data[
                station_data[station_config["columns"]["create_time"]].between(start_time, end_time)]
        online_data = match.get_online_data(station_config['ids'], start_time, end_time)
        match_result.append(match.match_data_v1(station_data, online_data, station_config))

        json_result = match.create_json_result_v2(match_result, station_config, start_time, end_time)
        return json_result
